package com.hsnn.medstgmini.trading.pur.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.trading.pur.dao.RuturnConfirmDao;
import com.hsnn.medstgmini.trading.pur.model.ReturnOrder;
import com.hsnn.medstgmini.trading.pur.model.ReturnStatus;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.PinYin;

/**
 * @category 退化确认
 * @author 蔡春龙
 * @date 2015年7月16日
 */
@Repository
public class RuturnConfirmDaoImpl implements RuturnConfirmDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 查询退货单确认列表
	 * @date 2015年7月16日
	 * @param returnOrder
	 * @return
	 */
	public Pagination getReturnConfirm(ReturnOrder returnOrder, Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.ret_code retCode, a.hosp_code hospCode, a.hosp_name hospName, ");
		sb.append(" a.detail_count detailCount, a.detail_amount detailAmount,  ");
		sb.append(" DATE_FORMAT(a.ret_datetime, '%Y-%m-%d %T') retDatetime, ");
		sb.append(" a.hosp_ar_fullname areaName, a.del_comp_code delCompCode, ");
		sb.append(" a.del_comp_name delCompName, a.`status` returnStatus, a.confirm_user confirmUser, ");
		sb.append(" DATE_FORMAT(a.confirm_time, '%Y-%m-%d %T') confirmTime, b.`name` retUser ");
		sb.append(" FROM pur_returninfo a  ");
		sb.append(" LEFT JOIN sys_user b ON a.ret_user = b.id ");
		sb.append(" WHERE a.`status` = :status AND a.del_comp_code = :delCompCode ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("status", ReturnStatus.submit.getKey());
		params.put("delCompCode", returnOrder.getDelCompCode());
		
		if (StringUtils.isNotBlank(returnOrder.getRetCode())) {
			sb.append(" AND a.ret_code = :retCode ");
			params.put("retCode", returnOrder.getRetCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospCode())) {
			sb.append(" AND a.hosp_code = :hospCode ");
			params.put("hospCode", returnOrder.getHospCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + returnOrder.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", returnOrder.getStartDate());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", returnOrder.getEndDate());
		}
		
		sb.append(" ORDER BY a.ret_datetime DESC ");
		
		return dao.getPageMapList(sb.toString(), params, page);
	}
	
	/**
	 * @category 查询退货单明细列表
	 * @date 2015年7月16日
	 * @param returnOrder
	 * @return
	 */
	public Pagination getReturnDetail(ReturnOrder returnOrder, Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.ret_count retCount, a.ret_reason retReason, ");
		sb.append("     a.approve_code approveCode,a.credential, a.drug_medicine drugMedicine,a.pro_price proPrice, a.drug_code drugCode, ");
		sb.append("     a.qualityspec_name drugName, a.form_name formName, a.spec_name specName, ");
		sb.append("     a.unit_name unitName, a.material_name materialName, a.factor, ");
		sb.append("     a.prod_compcode prodCompCode, a.prod_compname prodCompName, a.total_amount totalAmount ");
		sb.append(" FROM pur_returninfodetail a ");
		sb.append(" WHERE a.ret_code = :retCode ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("retCode", returnOrder.getRetCode());
		
		if (StringUtils.isNotBlank(returnOrder.getDrugCode())) {
			sb.append(" AND a.drug_code = :drugCode ");
			params.put("drugCode", returnOrder.getDrugCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getDrugName())) {
			sb.append(" AND ( a.qualityspec_name LIKE :drugName OR a.qualityspec_name_spel LIKE :drugName ) ");
			params.put("drugName", "%" + returnOrder.getDrugName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getCompName())) {
			sb.append(" AND ( a.prod_compname LIKE :compName OR a.prod_compname_spel LIKE :compName ) ");
			params.put("compName", "%" + returnOrder.getCompName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getApproveCode())) {
			sb.append(" AND a.approve_code = :approveCode ");
			params.put("approveCode", returnOrder.getApproveCode());
		}
		
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 同意或拒绝退货
	 * @date 2015年7月16日
	 * @param returnOrder
	 * @param user
	 * @param list
	 */
	public void returnConfirm(ReturnOrder returnOrder, SysUser user,
			List<ReturnOrder> list) {
		Session session = factory.getCurrentSession();
		if (list != null && !list.isEmpty()) {
			List<String> ids = new ArrayList<String>();
			for (ReturnOrder temp : list) {
				ids.add(temp.getRetCode());
			}
			
			//	校验是否已被处理过
			checkOldStatus(ids);
			
			//	锁定要操作的行
			lockRows(ids);
			
			//	更新退货单状态
			updateReturnStatus(returnOrder, user, list);
			
			//	更新退货明细状态
			updateReturnDetailStatus(returnOrder, user, ids);
			
			if(returnOrder.getReturnStatus() == ReturnStatus.reject.getKey()){
				// 更新 到货表 累计退货数量
				updatePurAoginfoReturnCount(returnOrder, user, ids);
			}
			session.flush();
		}
	}
	
	/**
	 * @category 校验是否已被处理过
	 * @date 2015年12月2日
	 * @param ids
	 */
	private void checkOldStatus(List<String> ids) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT COUNT(*) FROM pur_returninfo ");
		sb.append(" WHERE ret_code IN (:ids) AND `status` = :status ");
		
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setParameterList("ids", ids);
		query.setParameter("status", ReturnStatus.submit.getKey());
		BigInteger count = (BigInteger) query.uniqueResult();
		
		if (count.intValue() != ids.size()) {
			throw new MSException("部分退货已经确认，请刷新后重新确认！");
		}
	}
	
	/**
	 * @category 锁定行
	 * @date 2015年12月2日
	 * @param ids
	 */
	private void lockRows(List<String> ids) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM pur_returninfo ");
		sb.append(" WHERE ret_code IN (:ids) FOR UPDATE ");
		
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setParameterList("ids", ids);
		query.list();
	}
	
	/**
	 * @category 更新退货单状态
	 * @date 2015年12月2日
	 * @param returnOrder
	 * @param user
	 * @param list
	 */
	private void updateReturnStatus(ReturnOrder returnOrder, SysUser user,
			List<ReturnOrder> list) {
		Session session = factory.getCurrentSession();
		for (ReturnOrder ro : list) {
			StringBuilder sb = new StringBuilder();
			sb.append(" UPDATE pur_returninfo SET `status` = :status, confirm_user = :userId, ");
			sb.append(" confirm_remark = :confirmRemark, confirm_time = sysdate() ");
			sb.append(" WHERE ret_code = :retCode ");
			
			SQLQuery query = session.createSQLQuery(sb.toString());
			query.setInteger("status", returnOrder.getReturnStatus());
			query.setString("userId", user.getId());
			query.setString("confirmRemark", ro.getConfirmRemark());
			query.setString("retCode", ro.getRetCode());
			query.executeUpdate();
		}
	}
	
	/**
	 * @category 更新退货单明细状态
	 * @date 2015年12月2日
	 * @param list
	 * @param user
	 */
	private void updateReturnDetailStatus(ReturnOrder returnOrder, SysUser user,
			List<String> ids) {
		StringBuilder sb = new StringBuilder();
		sb.append(" UPDATE pur_returninfodetail SET `status` = :status, ");
		sb.append(" upd_time = SYSDATE(), upd_user = :userId WHERE ret_code IN (:ids) ");
		
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setInteger("status", returnOrder.getReturnStatus());
		query.setString("userId", user.getId());
		query.setParameterList("ids", ids);
		
		query.executeUpdate();
	}

	/**
	 * @category 更新 到货表 累计退货数量
	 * @author 韩守松
	 * @date   2015年12月4日
	 * @param  @param returnOrder
	 * @param  @param user
	 * @param  @param ids
	 */
	@SuppressWarnings("unchecked")
	public void updatePurAoginfoReturnCount(ReturnOrder returnOrder, SysUser user,
			List<String> ids){
		Session session = factory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select recp_info_id,ret_count from pur_returninfodetail where ret_code IN (:ids) ");
		SQLQuery query = session.createSQLQuery(hql.toString());
		query.setParameterList("ids", ids);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query.list();
		if(list.size() > 0){
			for(int i=0;i<list.size();i++){
				StringBuffer upHql = new StringBuffer();
				upHql.append(" UPDATE pur_aoginfo SET return_count = return_count - :retCount where recp_info_id = :recpInfoId ");
				SQLQuery upQuery = session.createSQLQuery(upHql.toString());
				upQuery.setInteger("retCount", Integer.valueOf((list.get(i).get("ret_count").toString())));
				upQuery.setString("recpInfoId", list.get(i).get("recp_info_id").toString());
				upQuery.executeUpdate();
				session.flush();
				session.clear();
			}
		}
	}
	
	/**
	 * @category 查询历史退货单列表
	 * @date 2015年7月16日
	 * @param returnOrder
	 * @return
	 */
	public Pagination getReturnOrderHis(ReturnOrder returnOrder, Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.ret_code retCode, a.hosp_code hospCode, a.hosp_name hospName, ");
		sb.append(" a.detail_count detailCount, a.detail_amount detailAmount,  ");
		sb.append(" DATE_FORMAT(a.ret_datetime, '%Y-%m-%d %T') retDatetime, a.confirm_remark confirmRemark, ");
		sb.append(" a.hosp_ar_fullname areaName, a.del_comp_code delCompCode, ");
		sb.append(" a.del_comp_name delCompName, a.`status` returnStatus, c.`name` confirmUser, ");
		sb.append(" DATE_FORMAT(a.confirm_time, '%Y-%m-%d %T') confirmTime, b.`name` retUser ");
		sb.append(" FROM pur_returninfo a  ");
		sb.append(" LEFT JOIN sys_user b ON a.ret_user = b.id ");
		sb.append(" LEFT JOIN sys_user c ON a.confirm_user = c.id ");
		sb.append(" WHERE a.del_comp_code = :delCompCode ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("delCompCode", returnOrder.getDelCompCode());
		
		if (returnOrder.getReturnStatus() != null) {
			sb.append(" AND a.`status` = :status ");
			params.put("status", returnOrder.getReturnStatus());
		} else {
			sb.append(" AND ( a.`status` = :status1 OR a.`status` = :status2 ) ");
			params.put("status1", ReturnStatus.agree.getKey());
			params.put("status2", ReturnStatus.reject.getKey());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getRetCode())) {
			sb.append(" AND a.ret_code = :retCode ");
			params.put("retCode", returnOrder.getRetCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospCode())) {
			sb.append(" AND a.hosp_code = :hospCode ");
			params.put("hospCode", returnOrder.getHospCode());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + returnOrder.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(returnOrder.getStartDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", returnOrder.getStartDate());
		}
		
		if (StringUtils.isNotBlank(returnOrder.getEndDate())) {
			sb.append(" AND DATE_FORMAT(a.ret_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", returnOrder.getEndDate());
		}
		
		sb.append(" ORDER BY a.ret_datetime DESC ");
		
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 查询历史退货单明细列表
	 * @date 2015年7月16日
	 * @param returnOrder
	 * @return
	 */
	public Pagination getReturnOrderHisDetail(ReturnOrder returnOrder,
			Pagination page) {

		StringBuffer listSb = new StringBuffer();
		Map<String, Object> params = new HashMap<String, Object>();
		listSb.append("SELECT a.ret_detail_id retDetailId, a.ret_detail_code retDetailCode, a.ret_code retCode, a.goods_code goodsCode, a.lot_id lotId, a.pro_price proPrice, a.ret_count retCount, a.ret_reason retReason,  Date_format(a.submit_time, '%Y-%m-%d %H:%i:%S') submitTime, a.submit_user submitUser, a.submit_remark submitRemark, a.status, Date_format(a.confirm_time, '%Y-%m-%d %H:%i:%S') confirmTime, a.confirm_user confirmUser, a.confirm_remark confirmRemark, b.drug_id drugId, b.drug_code drugCode, b.drug_name drugName, b.form_name formName, b.spec_name specName, b.factor, b.unit, b.material_name materialName, c.hosp_id hospId, c.hosp_name hospName, d.comp_id compId, d.comp_name compName, e.name submitUserName, f.name confirmUserName FROM pur_returninfo_old a JOIN view_drug b ON a.drug_id = b.drug_id JOIN std_hospinfo c ON a.hosp_id = c.hosp_id JOIN std_compinfo d ON b.prod_comp_id = d.comp_id LEFT JOIN sys_user e ON a.submit_user = e.id LEFT JOIN sys_user f ON a.confirm_user = f.id WHERE 1 = 1 AND a.del_comp_id = :delCompId ");

		String retCode = returnOrder.getRetCode();
		if (StringUtils.isNotBlank(retCode)) {
			listSb.append("AND a.ret_code = :retCode ");
			params.put("retCode", retCode);
		}
		String lotId = returnOrder.getLotId();
		if (StringUtils.isNotBlank(lotId)) {
			listSb.append("AND a.lot_id = :lotId ");
			params.put("lotId", lotId);
		}
		String drugCode = returnOrder.getDrugCode();
		if (StringUtils.isNotBlank(drugCode)) {
			listSb.append("AND b.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		String drugName = returnOrder.getDrugName();
		if (StringUtils.isNotBlank(drugName)) {
			listSb.append("AND (b.drug_name like :drugName  or b.drug_name_spel like :drugNameSpel)");
			params.put("drugName", "%"+drugName.trim()+"%");
			params.put("drugNameSpel", "%"+drugName.trim().toUpperCase()+"%");
		}
		String hospName = returnOrder.getHospName();
		if (StringUtils.isNotBlank(hospName)) {
			listSb.append("AND (c.hosp_name like :hospName or c.hosp_spel_code like :hospSpelCode) ");
			params.put("hospName", "%"+hospName.trim()+"%");
			params.put("hospSpelCode", "%"+hospName.trim().toUpperCase()+"%");
		}
		String compName = returnOrder.getCompName();
		if (StringUtils.isNotBlank(compName)) {
			listSb.append("AND (d.comp_name like :compName or d.comp_spel_code like :compSpelCode)");
			params.put("compName", "%"+compName.trim()+"%");
			params.put("compSpelCode", "%"+compName.trim().toUpperCase()+"%");
		}
		String startDate = returnOrder.getStartDate();
		if (startDate != null) {
			listSb.append("AND DATE_FORMAT(a.submit_time, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", startDate);
		}
		String endDate = returnOrder.getEndDate();
		if (endDate != null) {
			listSb.append("AND DATE_FORMAT(a.submit_time, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", endDate);
		}
		params.put("delCompId", returnOrder.getDelCompId());
		return dao.getSqlPageMapList(listSb.toString(), params, page);
	}
	/**
	 * 
	 *@category 退货记录查看（配送企业按药品查看）
	 *@author 邱磊
	 *@date 2015年7月31日 下午4:19:24
	 *@param page
	 *@return
	 */
	public Pagination getReturnDrugHis(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.drug_id as drugId,b.drug_code as drugCode,b.drug_name as drugName,b.form_name as formName,")
		.append("b.spec_name as specName,b.factor,b.unit,b.material_name as materialName,b.comp_name as compName,count(a.drug_id) as retCount")
		.append("  from pur_returninfo_old a")
		.append(" left join view_drug b on a.drug_id=b.drug_id")
		.append(" where a.del_comp_id=:delCompId")
		.append(" and a.status=2");
		Map<String, Object> params = new HashMap<String, Object>();
		ReturnOrder form = (ReturnOrder) page.getForm();
		params.put("delCompId", form.getDelCompId());
		if (StringUtils.isNotBlank(form.getDrugCode())){
			sql.append(" and b.drug_code=:drugCode");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getDrugName())){
			if (PinYin.isPinYin(form.getDrugName().trim())){
				sql.append(" and b.drug_name_spel like :drugName");
				params.put("drugName", "%"+form.getDrugName().trim().toUpperCase()+"%");
			} else {
				sql.append(" and b.drug_name like :drugName");
				params.put("drugName", "%"+form.getDrugName().trim()+"%");
			}
		}
		if (StringUtils.isNotBlank(form.getCompName())){
			sql.append(" and b.comp_name like :compName");
			params.put("compName", form.getCompName());
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
}
